﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ES_Project.Model;
using System.Data;
using System.Data.SqlClient;

using System.Collections;
using System.Web.Security;

using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;

using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;

using ES_Project.Model.entity;

namespace ES_Project.Pages
{
    public partial class FamilySearchDataSubject : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand();
       
        ReportDocument myRpt = new ReportDocument();
        protected void Page_Load(object sender, EventArgs e)
        {
            //วันที่
            Month.Text = DateTime.Now.ToString("MM/yyyy");
            Rec_Date.Text = DateTime.Now.ToString("dd/MM/yyyy");
            Receipt_Date.Text = DateTime.Now.ToString("yyyy/MM");
            //
            this.st_code.Text = Request.QueryString["st_code"];
            this.regisdate.Text = Request.QueryString["register_date"];
            this.fa_code.Text = Request.QueryString["family_code"];
            this.st_id.Text = Request.QueryString["st_id"];

            if (fa_code.Text == "")
            {
                Label9.Text = "";
            }
            else
            {
                Label9.Text = "รหัสครอบครัว :";
            }

            //

            maxdata();

            ////

          
                MySqlConnection Conn = new MySqlConnection(StrConn);

                string sqlsm;



                sqlsm = "select * from student_detail inner join emp_cls_mast on emp_cls_mast.emp_cls_mast_id = student_detail.emp_cls_mast_id inner join student_mast on student_detail.st_id = student_mast.st_id inner join cls_register_detail on cls_register_detail.cls_reg_detail_id = emp_cls_mast.cls_reg_detail_id inner join cls_register_mast on cls_register_mast.cls_reg_id  = cls_register_detail.cls_reg_id inner join emp_mast on emp_cls_mast.emp_id  =  emp_mast.emp_id  where student_mast.st_code= '" + st_code.Text + "'  ";

                Conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(sqlsm, Conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "student_detail");
                GridView1.DataSource = ds.Tables["student_detail"];
                GridView1.DataBind();

                da.Fill(ds);

                int _row = ds.Tables[0].Rows.Count;
                int n = 0;
                if (_row > n)
                {


                    st_name.Text = (string)ds.Tables[0].Rows[n]["st_name"].ToString();
                    fa_name.Text = (string)ds.Tables[0].Rows[n]["family_name"].ToString();
                    TextArea1.Value = (string)ds.Tables[0].Rows[n]["address"].ToString();
                    TextArea2.Value = (string)ds.Tables[0].Rows[n]["remark"].ToString();
                    

                }
            

            //
                int i;
                Double sum3 = 0.00;
                for (i = 0; i <= GridView1.Rows.Count - 1; i++)
                {


                    string discount = ((Label)GridView1.Rows[i].FindControl("Label4")).Text.Trim();
                    Double discount2 = Convert.ToDouble(discount);



                    sum3 = sum3 + discount2;
                    Label8.Text = Convert.ToString(sum3);
                    Label8.Text = String.Format("{0:0,0}", Convert.ToDouble(Label8.Text));


                }
               

                namediscount();
                calrate();
                changenumtotext();
                
        }
        void calrate()
        {



            double rate = Convert.ToDouble(Label13.Text) / 100;
            double fee = Convert.ToDouble(Label8.Text);
            double rate2 = 0.00;
           
            rate2 = fee * rate;

            Label14.Text = Convert.ToString(rate2);
            Label14.Text = String.Format("{0:0,0.00}", Convert.ToDouble(Label14.Text));
            if (Label14.Text != "")
            {

                String[] myArr = Label14.Text.Split('.');

                foreach (String myList in myArr)
                {
                    string num = " " + myArr[0] + " ";
                    string Decimal1 = " " + myArr[1] + " ";
                    double decimalnum = Convert.ToDouble(Decimal1);
                    double num2 = Convert.ToDouble(num);
                    double rate3 = 0;
                    if (decimalnum >= 5)
                    {
                        rate3 = num2 + 1;
                        decimalnum = 0;
                        Label14.Text = Convert.ToString(rate3);
                        Label14.Text = String.Format("{0:0,0}", Convert.ToDouble(Label14.Text));
                    }
                    else
                    {
                        rate3 = num2;
                        Label14.Text = Convert.ToString(rate3);
                        Label14.Text = String.Format("{0:0,0}", Convert.ToDouble(Label14.Text));
                    }
                }
            }
            // รวมเงิน
            double sumrate = Convert.ToDouble(Label8.Text);
            double sumrate2 = Convert.ToDouble(Label14.Text);
            double sumrate3 = 0.00;
            sumrate3 = sumrate - sumrate2;
            Label15.Text = Convert.ToString(sumrate3);
            Label15.Text = String.Format("{0:0,0}", Convert.ToDouble(Label15.Text));
           
        }
        
        void namediscount()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);

            string sqlsm;



            sqlsm = "select * from discount inner join student_mast on student_mast.id_Discount = discount.id_Discount where student_mast.st_id = '" + st_id.Text + "' ";

            Conn.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sqlsm, Conn);
            DataSet ds = new DataSet();
           

            da.Fill(ds);

            int _row = ds.Tables[0].Rows.Count;
            int n = 0;
            if (_row > n)
            {


                Label10.Text = (string)ds.Tables[0].Rows[n]["Name_school"].ToString();

                Label11.Text = (string)ds.Tables[0].Rows[n]["Address"].ToString();
                Label12.Text = (string)ds.Tables[0].Rows[n]["Name_Discount"].ToString();
                Label13.Text = (string)ds.Tables[0].Rows[n]["Num_Discount"].ToString();
            }
        }
        void maxdata()
        {
            MySqlConnection Conn2 = new MySqlConnection(StrConn);
            string sqlsm2;
            sqlsm2 = "select max(Rec_id) as maxrecid from receipt_running_mast where St_Code= '" + st_code.Text + "'  ";

            Conn2.Open();
            MySqlDataAdapter da2 = new MySqlDataAdapter(sqlsm2, Conn2);
            DataSet ds2 = new DataSet();
            da2.Fill(ds2);
            int _row2 = ds2.Tables[0].Rows.Count;
            int n2 = 0;
            if (_row2 > n2)
            {
                Max.Text = (string)ds2.Tables[0].Rows[n2]["maxrecid"].ToString();

            }
        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
           //Label Label4 = (Label)(e.Row.FindControl("Label4"));
           //if (Label4 != null)
           //{

           //    Label14.Text = String.Format("{0:0,0.00}", DataBinder.Eval(e.Row.DataItem, "cls_course_amt"));
           //}
          

        }
      
        protected void Button1_Click(object sender, EventArgs e)
        {
            
                //save receipt_running_mast
                MySqlConnection Conn = new MySqlConnection(StrConn);
                string strSQL;

                strSQL = "SELECT  * FROM receipt_running_mast  order by Rec_id desc ";

                Conn.Open();
                MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
                DataSet ds4 = new DataSet();
                da4.Fill(ds4);

                int _row5 = ds4.Tables[0].Rows.Count;
                int i5 = 0;
                if (_row5 > i5)
                {
                    Receipt_run.Text = Convert.ToString(ds4.Tables[0].Rows[i5]["Rec_last_running"]);

                    int m = Convert.ToInt32(Receipt_run.Text);
                    int m2 = m + 1;
                    string m3 = Convert.ToString(m2);
                   string m4 = m3.Length.ToString();
                   if (m4 == "1")
                    {
                        Receipt_run_last.Text = "000"+m3;
                    }
                   else if (m4 == "2")
                    {
                        Receipt_run_last.Text = "00" + m3;
                    }
                   else if (m4 == "3")
                   {
                       Receipt_run_last.Text = "0" + m3;
                   }
                   else { Receipt_run_last.Text = m3; }

          
                }

                //กรณี ในตารางreceipt_running_mastยังไม่มีค่า
                int _row4 = ds4.Tables[0].Rows.Count;
                if (_row4 == 0)
                {
                    MySqlConnection Conn2 = new MySqlConnection(StrConn);
                    string strSQL2;
                    strSQL2 = "INSERT INTO receipt_running_mast (Rec_Year,Rec_last_running,Rec_Month,Rec_Date,St_Code)   VALUES ('" + this.Receipt_Date.Text + "','0000','" + Convert.ToDateTime(Month.Text).AddYears(-543).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(Rec_Date.Text).AddYears(-543).ToString("yyyy-MM-dd") + "','" + this.st_code.Text + "')";

                    MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn2);
                    Conn2.Open();
                    objCmd.Connection = Conn;
                    objCmd.CommandType = CommandType.Text;
                    objCmd.CommandText = strSQL2;

                    objCmd.ExecuteNonQuery();
                }
                else
                {
                   

                    //ไม่ให้ Rec_last_running เลขซ้ำกัน
                   
                    MySqlConnection Conn3 = new MySqlConnection(StrConn);
                    int intNumRows;
                    string strSQL3;
                    strSQL3 = "SELECT COUNT(*) FROM receipt_running_mast WHERE Rec_last_running = '" + this.Receipt_run_last.Text + "' ";


                    Conn3.Open();
                    objCmd.Connection = Conn3;
                    objCmd.CommandType = CommandType.Text;
                    objCmd.CommandText = strSQL3;
                    objCmd = new MySqlCommand(strSQL3, Conn3);
                    intNumRows = Convert.ToInt32(objCmd.ExecuteScalar());

                    if (intNumRows > 0)
                    {
                        //Rec_last_running มีการซ้ำกัน
                        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "ReturnMessini9", "alert('กดปุ่มพิมพ์อีกครั้งค่ะ');", true);
                    }
                    else
                    {
                        //บันทึก receipt_running_mast
                        MySqlConnection Conn2 = new MySqlConnection(StrConn);
                        string strSQL2;
                        strSQL2 = "INSERT INTO receipt_running_mast (Rec_Year,Rec_last_running,Rec_Month,Rec_Date,St_Code)   VALUES ('" + this.Receipt_Date.Text + "','" + Receipt_run_last.Text + "','" + Convert.ToDateTime(Month.Text).AddYears(-543).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(Rec_Date.Text).AddYears(-543).ToString("yyyy-MM-dd") + "','" + this.st_code.Text + "' )";

                        MySqlDataAdapter da2 = new MySqlDataAdapter(strSQL2, Conn2);
                        Conn2.Open();
                        objCmd.Connection = Conn;
                        objCmd.CommandType = CommandType.Text;
                        objCmd.CommandText = strSQL2;

                        objCmd.ExecuteNonQuery();
                       
                    }
                          
            }
            //
                maxdata();
           
            //// ดึงเลขที่ใบเสร็จ ไปแสดงหน้า report
            MySqlConnection Connrun = new MySqlConnection(StrConn);
            string strSQLrun;

            strSQLrun = "select * from receipt_running_mast  where Rec_id = '" + Max.Text + "'  ";

            Connrun.Open();
            MySqlDataAdapter darun = new MySqlDataAdapter(strSQLrun, Connrun);
            DataSet dsrun = new DataSet();
            darun.Fill(dsrun);

            int _rowrun = dsrun.Tables[0].Rows.Count;
            int ir = 0;
            if (_rowrun > ir)
            {
                string Year = Convert.ToString(dsrun.Tables[0].Rows[ir]["Rec_Year"]);
                string last_running = Convert.ToString(dsrun.Tables[0].Rows[ir]["Rec_last_running"]);

                TextBox1.Text = "M"+Year+"/"+last_running;

            }




            // report receipt1.rpt
            List<stdata> st = new List<stdata>();
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {

                stdata st2 = new stdata();
                st2.cls_reg_code = ((Label)GridView1.Rows[i].FindControl("Label2")).Text.Trim();

                st2.cls_name = ((Label)GridView1.Rows[i].FindControl("Label3")).Text.Trim();
                st2.st_day = ((Label)GridView1.Rows[i].FindControl("st_day")).Text.Trim();
                st2.st_start = ((Label)GridView1.Rows[i].FindControl("st_start")).Text.Trim();
                st2.st_end = ((Label)GridView1.Rows[i].FindControl("st_end")).Text.Trim();
               
                st2.cls_course_amt = ((Label)GridView1.Rows[i].FindControl("Label4")).Text.Trim();
             
                st2.cls_course_no = ((Label)GridView1.Rows[i].FindControl("Label6")).Text.Trim();
              
                st2.st1 = ((Label)GridView1.Rows[i].FindControl("course")).Text.Trim();
                st.Add(st2);
            }
            if (fa_code.Text != "")
            {
                myRpt.Load(Server.MapPath("~/report/receipt1.rpt"));
                myRpt.SetDataSource(st);
                myRpt.SetParameterValue("Month", Month.Text);
                myRpt.SetParameterValue("Date", Rec_Date.Text);
                myRpt.SetParameterValue("facode", fa_code.Text);
                myRpt.SetParameterValue("faname", fa_name.Text);
                myRpt.SetParameterValue("stcode", st_code.Text);
                myRpt.SetParameterValue("stname", st_name.Text);
                myRpt.SetParameterValue("add", TextArea1.Value);
                myRpt.SetParameterValue("remark", TextArea2.Value);
                myRpt.SetParameterValue("Label8", Label8.Text);
                myRpt.SetParameterValue("running", TextBox1.Text);
                myRpt.SetParameterValue("Label10", Label10.Text);
                myRpt.SetParameterValue("Label11", Label11.Text);
                myRpt.SetParameterValue("Label12", Label12.Text);
                myRpt.SetParameterValue("Label13", Label13.Text);
                myRpt.SetParameterValue("Label14", Label14.Text);
                myRpt.SetParameterValue("Label15", Label15.Text);
            }
            else
            {
                myRpt.Load(Server.MapPath("~/report/receipt2.rpt"));
                myRpt.SetDataSource(st);
                myRpt.SetParameterValue("Month", Month.Text);
                myRpt.SetParameterValue("Date", Rec_Date.Text);
                myRpt.SetParameterValue("stcode", st_code.Text);
                myRpt.SetParameterValue("stname", st_name.Text);
                myRpt.SetParameterValue("add", TextArea1.Value);
                myRpt.SetParameterValue("remark", TextArea2.Value);
                myRpt.SetParameterValue("Label8", Label8.Text);
                myRpt.SetParameterValue("running", TextBox1.Text);
                myRpt.SetParameterValue("Label10", Label10.Text);
                myRpt.SetParameterValue("Label11", Label11.Text);
                myRpt.SetParameterValue("Label12", Label12.Text);
                myRpt.SetParameterValue("Label13", Label13.Text);
                myRpt.SetParameterValue("Label14", Label14.Text);
                myRpt.SetParameterValue("Label15", Label15.Text);
            }
           


            Session["rpt"] = myRpt;

            Response.Redirect("~/Pages/Report.aspx");
            myRpt.Close(); 

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Session["st_id"] = this.st_id.Text;
            Response.Redirect("~/Pages/EditStudentFromPrint.aspx");
        }

        void changenumtotext()
        {
            string bahtTxt, n;
            bahtTH.Text = "";
            double amount;
            try { amount = Convert.ToDouble(Label15.Text); }
            catch { amount = 0; }
            bahtTxt = amount.ToString("####.00");
            string[] num = { "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า", "สิบ" };
            string[] rank = { "", "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน" };
            string[] temp = bahtTxt.Split('.');
            string intVal = temp[0];
            string decVal = temp[1];
            if (Convert.ToDouble(bahtTxt) == 0)
                bahtTH.Text = "ศูนย์บาทถ้วน";

            else
            {
                for (int i = 0; i < intVal.Length; i++)
                {
                    n = intVal.Substring(i, 1);
                    if (n != "0")
                    {
                        if ((i == (intVal.Length - 1)) && (n == "1"))
                            bahtTH.Text += "เอ็ด";
                        else if ((i == (intVal.Length - 2)) && (n == "2"))
                            bahtTH.Text += "ยี่";
                        else if ((i == (intVal.Length - 2)) && (n == "1"))
                            bahtTH.Text += "";
                        else
                            bahtTH.Text += num[Convert.ToInt32(n)];
                        bahtTH.Text += rank[(intVal.Length - i) - 1];
                    }
                }
                bahtTH.Text += "บาท";
                if (decVal == "00")
                    bahtTH.Text += "ถ้วน";
                else
                {
                    for (int i = 0; i < decVal.Length; i++)
                    {
                        n = decVal.Substring(i, 1);
                        if (n != "0")
                        {
                            if ((i == decVal.Length - 1) && (n == "1"))
                                bahtTH.Text += "เอ็ด";
                            else if ((i == (decVal.Length - 2)) && (n == "2"))
                                bahtTH.Text += "ยี่";
                            else if ((i == (decVal.Length - 2)) && (n == "1"))
                                bahtTH.Text += "";
                            else
                                bahtTH.Text += num[Convert.ToInt32(n)];
                            bahtTH.Text += rank[(decVal.Length - i) - 1];
                        }
                    }
                    bahtTH.Text += "สตางค์";
                }
            }
        }
    }
}